{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "import pandas as pd\n",
    "from pathlib import Path\n",
    "import pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dir = Path('../data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "csvs = [x for x in data_dir.iterdir() if 'timeseries' in str(x)]\n",
    "counts = []\n",
    "regions = []\n",
    "results = pd.DataFrame(columns=['roi', 'new_deaths', 'new_cases', 'new_recover', 'abs_days', 'rel_days'])\n",
    "i = 1\n",
    "for csv in csvs:\n",
    "    region = str(csv).split('.')[0].split('_')[-1]\n",
    "    regions.append(region)\n",
    "    df = pd.read_csv(csv)\n",
    "    df = df.iloc[:-1]\n",
    "    df = df[df['cum_cases']>=10]\n",
    "    df['abs_days'] = df['dates2'].apply(lambda x: (datetime.strptime(x, '%m/%d/%y') - datetime.strptime('01/22/20', '%m/%d/%y')).days)\n",
    "    df['rel_days'] = df['dates2'].apply(lambda x: (datetime.strptime(x, '%m/%d/%y') - datetime.strptime(df['dates2'].values[0], '%m/%d/%y')).days)\n",
    "    counts.append(df.shape[0])\n",
    "    subset = ['new_deaths', 'new_cases', 'new_recover']\n",
    "    df[subset] = df[subset].clip(lower = 0)\n",
    "    df['roi'] = i\n",
    "    subset = ['roi', 'new_deaths', 'new_cases', 'new_recover', 'abs_days', 'rel_days']\n",
    "    results = results.append(df[subset])\n",
    "    i += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle\n",
    "with open(data_dir / 'stacked.pkl', 'wb') as f:\n",
    "    pickle.dump({'data': results, 'regions': regions, 'counts': counts}, f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>roi</th>\n",
       "      <th>new_deaths</th>\n",
       "      <th>new_cases</th>\n",
       "      <th>new_recover</th>\n",
       "      <th>abs_days</th>\n",
       "      <th>rel_days</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>48</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>48</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>51</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>51</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>52</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>52</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>80</td>\n",
       "      <td>171</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>80</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>81</td>\n",
       "      <td>171</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>81</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>82</td>\n",
       "      <td>171</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>82</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>83</td>\n",
       "      <td>171</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>83</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>84</td>\n",
       "      <td>171</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>84</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5963 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    roi new_deaths new_cases new_recover abs_days rel_days\n",
       "48    1          0        10           0       48        0\n",
       "49    1          0         3           1       49        1\n",
       "50    1          0         1           0       50        2\n",
       "51    1          0         8           0       51        3\n",
       "52    1          0         3           0       52        4\n",
       "..  ...        ...       ...         ...      ...      ...\n",
       "80  171          0         2           0       80        8\n",
       "81  171          0         0           0       81        9\n",
       "82  171          0         2           0       82       10\n",
       "83  171          0         0           3       83       11\n",
       "84  171          0         0           0       84       12\n",
       "\n",
       "[5963 rows x 6 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
